package databank;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class DataBank {
	
	
	int fileSize=10*1024*1024;
	private String uploadPath="C:\\workspace\\svn_web1403\\WebContent\\upload";
	String fileEncoding="utf-8";

	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	String db_user = "hr";
	String db_pwd = "hr";
	
	Connection conn;
	DatabankVo vo;
	
	String sql="";
	
	
	public String getUploadPath() {
		return uploadPath;
	}

	public void setUploadPath(String uploadPath) {
		this.uploadPath = uploadPath;
	}


	
	public DataBank() {
		try {

			// 1)드라이버 로딩
			Class.forName(driver).newInstance();

			// 2)Connection 생성
			conn = DriverManager.getConnection(url, db_user, db_pwd);
		} catch (Exception ex) {
			ex.getStackTrace();
		}
	}

	public DatabankVo getVo() {
		return vo;
	}

	public void setVo(DatabankVo vo) {
		this.vo = vo;
	}

	public boolean insert(){
		boolean flag=false;
		try{
			MultipartRequest mr= new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding, new DefaultFileRenamePolicy());
			String mid=mr.getParameter("mid");
			String subject=mr.getParameter("subject");
			String content=mr.getParameter("content");
			String pwd=mr.getParameter("pwd");

			String orifile=mr.getOriginalFileName("filename");
			String sysfile=mr.getFilesystemName("filename");
			
			//DB저장
			sql="insert into databank(serial,mid,mdate,pwd,subject,content,filename) "
					+ "values(seq_databank.nextval,?,sysdate,?,?,?,?)";
			
			PreparedStatement ps= conn.prepareStatement(sql);
			ps.setString(1, mid);
			ps.setString(2, pwd);
			ps.setString(3, subject);
			ps.setString(4, content);
			ps.setString(5, sysfile);
			
			int cnt= ps.executeUpdate();
			if(cnt>0) flag=true;

			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return flag;
	}
	
	public Vector<DatabankVo> list() {
		Vector<DatabankVo> v = new Vector<>();
		String sql = "select * from databank where mid like ? or subject like ? or content like ? order by serial desc";
		try {
			PreparedStatement ps= conn.prepareStatement(sql);
			ps.setString(1, "%"+ vo.getFind()+ "%");
			ps.setString(2, "%"+ vo.getFind()+ "%");
			ps.setString(3, "%"+ vo.getFind()+"%");

			ResultSet rs= ps.executeQuery();
			
			while(rs.next()){
				
				DatabankVo dv=new DatabankVo();
				
				dv.setSerial(rs.getInt("serial"));
				dv.setMid(rs.getString("mid"));
				dv.setMdate(rs.getDate("mdate"));
				dv.setPwd(rs.getString("pwd"));
				dv.setSubject(rs.getString("subject"));
				dv.setContent(rs.getString("content"));
				dv.setFilename(rs.getString("filename"));
			

				v.add(dv);
			}
			
			rs.close();
			ps.close();
			conn.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return v;
	}
	public DatabankVo view(){
		DatabankVo v=null;
		
		sql="select * from databank where serial=?";
		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setInt(1, vo.getSerial());
			
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				v= new DatabankVo();
				
				v.setSerial(rs.getInt("serial"));
				v.setMid(rs.getString("mid"));
				v.setMdate(rs.getDate("mdate"));
				v.setSubject(rs.getString("subject"));
				v.setContent(rs.getString("content"));
				v.setFilename(rs.getString("filename"));
				
				
			}
			rs.close();
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
			
		}
		return v;
	}
	public boolean delete(){
		
		boolean flag=false;
		
		sql="delete from databank where serial=?";
		
		try{
			
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setInt(1, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			if(cnt>0){
				File file= new File(getUploadPath()+"/"+vo.getFilename());
				if(file.exists()){
					file.delete();
					flag=true;
				}else{	
					//고민상담...
				}
				
			}
			
			
		}catch(Exception ex){
			
			ex.printStackTrace();
		}
		
		
		
		return flag;
		
	}
	public boolean update(){
		
		
		boolean flag=false;

		try{
			MultipartRequest mr= new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding, new DefaultFileRenamePolicy());
			
			int serial=Integer.parseInt(mr.getParameter("serial"));
			String subject=mr.getParameter("subject");
			String content=mr.getParameter("content");
			String pwd=mr.getParameter("pwd");
			String oldfile=mr.getParameter("filename");
			String sysfile=mr.getFilesystemName("newfilename");
			
			
			if(oldfile==null || oldfile==""){
				
				if(sysfile != null && sysfile != ""){
					
					sql="update databank set subject=?, content=?, filename=? where serial=? and pwd=?";
					
					PreparedStatement ps= conn.prepareStatement(sql);
					ps.setString(1, subject);
					ps.setString(2, content);
					ps.setString(3, sysfile);
					ps.setInt(4, serial);
					ps.setString(5, pwd);
					
					int cnt = ps.executeUpdate();
					
					if(cnt>0)flag=true;
					
				}else{
					sql="update databank set subject=?, content=? where serial=? and pwd=?";
					
					PreparedStatement ps= conn.prepareStatement(sql);
					ps.setString(1, subject);
					ps.setString(2, content);
					ps.setInt(3, serial);
					ps.setString(4, pwd);
					
					
					int cnt = ps.executeUpdate();
					
					if(cnt>0)flag=true;
					
				}	
					
				}
		}catch(Exception ex){
			
			ex.printStackTrace();
		}
		
		
		return flag;
	}
	
	public void fileDel(){
		sql="update databank set filename='' where serial=?";
		
		try{
			
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ps.setInt(1, vo.getSerial());
			
			int cnt = ps.executeUpdate();
			
			if(cnt>0){
				File file= new File(getUploadPath()+"/"+vo.getFilename());
				if(file.exists()){
					file.delete();
				
				}else{	
				}	
			}
			
		}catch(Exception ex){
			
			ex.printStackTrace();
		}
		

		
	}
	
	
	

}
